﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Script.Serialization;
using System.Web.Security;
using bzxxxt.admin.entity;
using bzxxxt.dal;
using bzxxxt.model;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
namespace bzxxxt.admin.service
{
    public class UserService : BaseService
    {
        public static UserEntity GetUser(HttpContext context) {
            try
            {
                if (context.Request.IsAuthenticated)
                {
                    FormsIdentity id = (FormsIdentity)context.User.Identity;
                    FormsAuthenticationTicket tickets = id.Ticket;

                    //反序列化获取票证里序列化的用户对象
                    UserEntity userFromCookie = new JavaScriptSerializer().Deserialize<UserEntity>(tickets.UserData);
                    return userFromCookie;
                }
                else
                    return null;
            }
            catch
            {
                return null;
            }
        }
        

        public UserEntity login(string userName, string userPwd)
        {
            var u = DB.tbUser.FirstOrDefault(m => m.userName == userName && m.userPwd == userPwd);
            if (u == null) return null;
            UserEntity user = new UserEntity
            {
                id = u.id,
                userName = u.userName,
                userPwd = u.userPwd,
                tel = u.tel,
                able = u.able,
                realName = u.realName,
                addDate = u.addDate,
                description = u.description,
                grade = u.grade,
                points = u.points,
                deptIds = u.tbDepartment.Select(m => m.id),
                deptNames = u.tbDepartment.Select(m => m.name),
                roleIds = u.tbRole.Select(m => m.id),
                roleNames = u.tbRole.Select(m => m.name),
                paradiseIds = u.tbUserParadise.Select(m => m.paradiseId).ToList(),
                areacodes = u.tbDepartment.Select(m=>m.code).ToList()
            };
            if (user.roleNames.Contains("镇级管理员")) {
                var codes = u.tbDepartment.Select(m=>m.code).ToList();
                user.paradiseIds = DB.tbParadise.Where(m =>codes.Contains(m.code.Substring(0,2))).Select(m=>m.id).ToList();
            }            
            return user;
        }
        public UserEntity ssologin(string userName)
        {
            var u = DB.tbUser.FirstOrDefault(m => m.userName == userName);
            if (u == null) return null;
            return new UserEntity
            {
                id = u.id,
                userName = u.userName,
                userPwd = u.userPwd,
                tel = u.tel,
                able = u.able,
                realName = u.realName,
                addDate = u.addDate,
                description = u.description,
                grade = u.grade,
                points = u.points,
                deptIds = u.tbDepartment.Select(m => m.id),
                deptNames = u.tbDepartment.Select(m => m.name),
                roleIds = u.tbRole.Select(m => m.id),
                roleNames = u.tbRole.Select(m => m.name),
                paradiseIds = u.tbUserParadise.Select(m => m.paradiseId).ToList(),
                areacodes = u.tbDepartment.Select(m => m.code).ToList()
            };
        }

        public string changepwd(int userId, string newPwd) {
            var user = DB.tbUser.First(m => m.id == userId);
            user.userPwd = newPwd;
            DB.SaveChanges();
            string json = JsonUtil.ToJson(new { success = true,msg="修改成功，正在跳转到登陆页面！"});
            return json;
        }
        public string getall()
        {
            var list = from b in DB.tbUser
                       select new UserEntity
                       {
                           id = b.id,
                           userName = b.userName,
                           userPwd = b.userName,
                           realName = b.realName,
                           points = b.points,
                           grade = b.grade,
                           addDate = b.addDate,
                           description = b.description,
                           able = b.able,
                           tel = b.tel,
                           roleIds = b.tbRole.Select(m=>m.id),
                           roleNames = b.tbRole.Select(m=>m.name),
                           deptIds = b.tbDepartment.Select(m=>m.id),
                           deptNames = b.tbDepartment.Select(m=>m.name)
                       };
            string json = JsonUtil.ToJson(list.ToList());
            return json;
        }

        public string search(int pageSize, int pageIndex, string order, string where = "id>1")
        {
            int totalCount = 0;
            SqlParameter paramTotalCount = new SqlParameter("@totalCount", totalCount);
            paramTotalCount.Direction = ParameterDirection.Output;
            var pager = DB.tbUser.SqlQuery("exec sp_Pager @tableName,@columns,@order,@pageSize,@pageIndex,@where,@totalCount output",
                GetPagerParameters("tbUser", "*", order, where, pageSize, pageIndex, paramTotalCount)).ToList();
            var list = from b in pager
                       select new UserEntity
                       {
                           id = b.id,
                           userName = b.userName,
                           userPwd = b.userName,
                           realName = b.realName,
                           points = b.points,
                           grade = b.grade,
                           addDate = b.addDate,
                           description = b.description,
                           able = b.able,
                           tel = b.tel,
                           roleIds = b.tbRole.Select(m => m.id),
                           roleNames = b.tbRole.Select(m => m.name),
                           deptIds = b.tbDepartment.Select(m => m.id),
                           deptNames = b.tbDepartment.Select(m => m.name)
                       };
            var l = list.ToList();
            totalCount = Convert.ToInt32(paramTotalCount.Value);
            string json = JsonUtil.ToJson(new { total = totalCount, rows = l });
            return json;
        }

        public string add(tbUser b)
        {
            if (string.IsNullOrEmpty(b.userPwd)) {
                b.userPwd = "888888";
            }
            b.userPwd = Md5.GetMD5String(b.userPwd);
            DB.tbUser.Add(b);
            DB.SaveChanges();
            string json = JsonUtil.ToJson(new { success = true });
            return json;
        }

        public string edit(tbUser b)
        {
            var old = DB.tbUser.FirstOrDefault(m => m.id == b.id);
            old.userName = b.userName;
            if (!string.IsNullOrEmpty(b.userPwd)) {
                old.userPwd = Md5.GetMD5String(b.userPwd);
            }
            
            old.tel = b.tel;
            old.able = b.able;
            old.realName = b.realName;
            old.points = b.points;
            old.grade = b.grade;             
            old.description = b.description;

            DB.SaveChanges();
            string json = JsonUtil.ToJson(new { success = true });
            return json;
        }

        public string delete(string strIds)
        {
            if (string.IsNullOrEmpty(strIds))
            {
                return JsonUtil.ToJson(new { success = false, msg = "没有选择要删除的记录！" });
            }
            string[] arr = strIds.Split(',');
            List<int> ids = new List<int>();
            foreach (var id in arr)
            {
                ids.Add(int.Parse(id));
            }
            var buttons = DB.tbUser.Where(m => ids.Contains(m.id)).ToList();
            if (buttons != null)
            {
                buttons.ForEach(m => DB.tbUser.Remove(m));
                DB.SaveChanges();
                return JsonUtil.ToJson(new { success = true });
            }
            else
            {
                return JsonUtil.ToJson(new { success = false, msg = "删除失败！" });
            }
        }
        /// <summary>
        /// 给用户设置部门
        /// </summary>
        /// <param name="userIds">用户id列表</param>
        /// <param name="deptIds">部门id列表</param>
        /// <returns></returns>
        public string setdetp(string userIds, string deptIds) {
            List<int> uidList = new List<int>();
            List<int> deptidList = new List<int>();
            if (!string.IsNullOrEmpty(userIds)) { 
                string[] ids = userIds.Split(',');
                foreach (string id in ids) { 
                    uidList.Add(int.Parse(id));
                }
            }
            if (!string.IsNullOrEmpty(deptIds))
            {
                string[] ids = deptIds.Split(',');
                foreach (string id in ids)
                {
                    deptidList.Add(int.Parse(id));
                }
            }
            //处理每个用户
            uidList.ForEach(m => setdept(m, deptidList));
            DB.SaveChanges();
            return JsonUtil.ToJson(new { success = true});
        }

        private void setdept(int uid, List<int> newIds) {
            var user = DB.tbUser.FirstOrDefault(m => m.id == uid);
            var olds = user.tbDepartment.ToList();
            var oldIds = olds.Select(m => m.id).ToList();
           
            if (olds.Count > 0)
            {
                //计算共有的，保留
                var commIds = oldIds.Intersect(newIds).ToList();
                //从原来的中去除共有的，得到要删除的
                var removeIds = oldIds.Where(m => !commIds.Contains(m)).ToList();
                if (removeIds.Count > 0)
                {
                    olds.ForEach(m => { if (removeIds.Contains(m.id)) user.tbDepartment.Remove(m); });
                }
                //从新的去除共有的，得到要添加的
                List<int> nids = new List<int>();
                nids.AddRange(newIds);
                nids.ForEach(m => { if (commIds.Contains(m)) newIds.Remove(m); });
            }
            newIds.ForEach(m =>
            {
                var b = DB.tbDepartment.FirstOrDefault(dept => dept.id == m);
                user.tbDepartment.Add(b);
            });
        }

        /// <summary>
        /// 给用户设置角色
        /// </summary>
        /// <param name="userIds">用户id列表</param>
        /// <param name="deptIds">角色id列表</param>
        /// <returns></returns>
        public string setrole(string userIds, string roleIds)
        {
            List<int> uidList = new List<int>();
            List<int> roleidList = new List<int>();
            if (!string.IsNullOrEmpty(userIds))
            {
                string[] ids = userIds.Split(',');
                foreach (string id in ids)
                {
                    uidList.Add(int.Parse(id));
                }
            }
            if (!string.IsNullOrEmpty(roleIds))
            {
                string[] ids = roleIds.Split(',');
                foreach (string id in ids)
                {
                    roleidList.Add(int.Parse(id));
                }
            }
            //处理每个用户
            uidList.ForEach(m => setrole(m, roleidList));
            DB.SaveChanges();
            return JsonUtil.ToJson(new { success = true });
        }

        private void setrole(int uid, List<int> newIds)
        {
            var user = DB.tbUser.FirstOrDefault(m => m.id == uid);
            var olds = user.tbRole.ToList();
            var oldIds = olds.Select(m => m.id).ToList();

            if (olds.Count > 0)
            {
                //计算共有的，保留
                var commIds = oldIds.Intersect(newIds).ToList();
                //从原来的中去除共有的，得到要删除的
                var removeIds = oldIds.Where(m => !commIds.Contains(m)).ToList();
                if (removeIds.Count > 0)
                {
                    olds.ForEach(m => { if (removeIds.Contains(m.id)) user.tbRole.Remove(m); });
                }
                //从新的去除共有的，得到要添加的
                List<int> nids = new List<int>();
                nids.AddRange(newIds);
                nids.ForEach(m => { if (commIds.Contains(m)) newIds.Remove(m); });
            }
            newIds.ForEach(m =>
            {
                var b = DB.tbRole.FirstOrDefault(role => role.id == m);
                user.tbRole.Add(b);
            });
        }

        /// <summary>
        /// 读取excel
        /// </summary>
        /// <param name="strFileName">excel文件路径</param>
        /// <param name="sheet">需要导出的sheet序号</param>
        /// <param name="HeaderRowIndex">列头所在行号，-1表示没有列头</param>
        /// <returns></returns>
        public List<UserEntity> ImportExceltoDb(string strFileName, int SheetIndex, int HeaderRowIndex)
        {
            List<UserEntity> list = new List<UserEntity>();
            IWorkbook wb;
            using (FileStream file = new FileStream(strFileName, FileMode.Open, FileAccess.Read))
            {
                wb = WorkbookFactory.Create(file);
            }
            ISheet sheet = wb.GetSheetAt(SheetIndex);
            
            DataTable table = new DataTable();
            IRow headerRow;
            int cellCount;
            StringBuilder sbMsg = new StringBuilder();
            try
            {
                //检查表格的标题是否符合模板格式

                headerRow = sheet.GetRow(HeaderRowIndex);
                cellCount = headerRow.LastCellNum;            		
                string[] headers = { "登录名", "密码", "姓名", "电话"};
                if (cellCount != headers.Length)
                {
                    throw new Exception("数据文件格式列数与模板列数不符！");
                }
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                    string headtitle = headerRow.GetCell(i).ToString();
                    if (headers[i] != headtitle)
                    {
                        throw new Exception("数据文件第" + i + "列名称与数据模板不符！");
                    }
                }
                int rowCount = sheet.LastRowNum;

                StringBuilder sbLine = new StringBuilder();
                for (int i = (HeaderRowIndex + 1); i <= sheet.LastRowNum; i++)
                {
                    UserEntity m = new UserEntity();
                    sbLine.Clear();
                    try
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null)
                        {
                            sbMsg.AppendFormat("第{0}行无数据!<br/>", i + 1);
                            continue;
                        }

                        string userName = getCellVaue(row.GetCell(0));
                        if (string.IsNullOrEmpty(userName)) {
                            sbLine.AppendFormat("第{0}行登录名无效! ", i + 1);
                        }
                        string pwd = getCellVaue(row.GetCell(1));
                        //处理村名
                        
                        if (string.IsNullOrEmpty(pwd))
                        {
                            pwd = "123456";
                        }
                        
                        string rname = getCellVaue(row.GetCell(2));
                        if (string.IsNullOrEmpty(rname))
                        {
                            sbLine.AppendFormat("第{0}行姓名不能为空! ", i + 1);
                        }
                        string tel = getCellVaue(row.GetCell(3));
                        m.userName = userName;
                        m.userPwd = pwd;
                        m.realName = rname;
                        m.tel = tel;
                        m.able = true;
                        m.addDate = DateTime.Now;                       
                    }
                    catch (Exception ex1)
                    {
                        sbLine.AppendFormat(" 第{0}行数据格式错误!" + ex1.Message, i + 1);
                    }

                    if (sbLine.Length > 0)
                    {
                        sbMsg.AppendLine(sbLine.ToString() + "<br/>");
                        sbLine.Clear();
                    }
                    else
                    {
                        list.Add(m);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            if (sbMsg.Length > 0)
            {
                string path = HttpContext.Current.Server.MapPath("/admin/upload/log.html");
                FileStream fs = new FileStream(path, FileMode.Create);
                StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
                sw.WriteLine("导入失败:<br/>");
                sw.WriteLine(sbMsg.ToString());
                sw.Close();
                throw new Exception("<a href='/admin/upload/log.html' target='_blank'>查看日志</a>");
            }
            sheet = null;
            return list;
        }

        private string getCellVaue(ICell cell)
        {
            string v = "";
            switch (cell.CellType)
            {
                case CellType.String:
                    v = cell.StringCellValue;

                    break;
                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        DateTime dt = DateTime.FromOADate(cell.NumericCellValue);
                        v = dt.ToString("yyyy-MM-dd");
                    }
                    else
                    {
                        v = Convert.ToString(cell.NumericCellValue);
                    }
                    break;
                case CellType.Boolean:
                    v = Convert.ToString(cell.BooleanCellValue);
                    break;
                case CellType.Error:
                    v = ErrorEval.GetText(cell.ErrorCellValue);
                    break;
                default:
                    v = "";
                    break;
            }
            return v.Trim();
        }

        public string Import(List<UserEntity> list)
        {
            StringBuilder sbMsg = new StringBuilder();
            foreach (UserEntity mm in list)
            {
                try
                {
                    tbUser u = DB.tbUser.FirstOrDefault(m => m.userName == mm.userName);
                    if (u != null) {
                        sbMsg.AppendFormat("登录名:{0}已经存在，导入失败！<br/>", mm.userName);
                        continue;
                    }
                    u = new tbUser();
                    u.userName = mm.userName;
                    u.userPwd = mm.userPwd;
                    u.tel = mm.tel;
                    u.realName = mm.realName;
                    u.able = mm.able;
                    u.addDate = mm.addDate;
                    add(u);
                }
                catch (Exception ex)
                {
                    sbMsg.AppendFormat("登录名:{0}密码:{1}导入未成功,可能原因:{2}<br/>", mm.userName, mm.userPwd, ex.Message);
                }
            }
            if (sbMsg.Length > 0)
            {
                string path = HttpContext.Current.Server.MapPath("/admin/upload/log.html");
                FileStream fs = new FileStream(path, FileMode.Create);
                StreamWriter sw = new StreamWriter(fs, Encoding.UTF8);
                sw.WriteLine("部分数据导入失败:<br/>");
                sw.WriteLine(sbMsg.ToString());
                sw.Close();
                throw new Exception("<a href='/admin/upload/log.html' target='_blank'>查看日志</a>");
            }
            return JsonUtil.ToJson(new { msg = "用户数据导入成功!", success = true });
        }
    }
}